 /**
	EXEC SP_CREATE_UPDATE_FG_LOT 'LGS-8913-A1','A687236.1',652,null,'05/21/2007',null, null,'Finished Goods','JSI Logistics','Running',null,'A687236.1',null
 */

Alter PROC SP_CREATE_UPDATE_FG_LOT (
 
 @PART_NUMBER 		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50), 
 @QTY_IN 			INT,
 @QTY_OUT 			INT,
 @DATE_IN 			DATETIME,
 @DATE_OUT 			DATETIME,
 @EXPECTED_DATE_OUT	DATETIME,
 @PS_TYPE 			VARCHAR(50),
 @PS_TYPE_NAME	 	VARCHAR(50),
 @VENDOR_NAME 		VARCHAR(50),
 @STATUS 			VARCHAR(50),
 @STAGE_NAME 		VARCHAR(50), 
 @FAB_LOT_NO 		VARCHAR(50),
 @LOT_CATEGORY 		VARCHAR(10),
 @LEAD_COUNT		VARCHAR(50),
 @PACKAGE_TYPE		VARCHAR(50),
 @TEST_PROGRAM		VARCHAR(50),
 @DATE_CODE			VARCHAR(50),
 @VENDOR_LOT		VARCHAR(50),
 @PO_NUMBER			VARCHAr(50)
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @THIS_LOT_STATUS VARCHAR(50)
    DECLARE @LOT_DATEIN DATETIME
	
 	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end


 	SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE= @PS_TYPE AND P.PART_NUMBER = @PART_NUMBER AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @PS_TYPE_NAME AND L_VENDOR_NAME = @VENDOR_NAME
	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 		SELECT @PROCESS_STEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE PMT,PROCESS_STEP PS WHERE PS_TYPE_NAME = @PS_TYPE_NAME AND PS_TYPE= @PS_TYPE AND PMT.PART_NUMBER = @PART_NUMBER AND PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID 
		IF @PROCESS_STEP_ID IS NULL
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @FAB_LOT_NO AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @PS_TYPE AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @FAB_LOT_NO, @vendor_name, @PS_TYPE, 'Discrepancy Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
			END			
 			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
 			RETURN
 		END
		ELSE
		BEGIN
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		END
 	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
	END 	
 	
 	SELECT @THIS_LOT_STATUS = LOT_CURRENT_STATUS, @LOT_ID = LOT_ID, @LOT_DATEIN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

    IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIn
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
         IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		 BEGIN	
			SET @DATE_IN = @LOT_DATEIN
		 END
    END

 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, STAGE_NAME, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, Lot_Drop_Out, LOT_DATE_CODE, PO_NUMBER ) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NO, ISNULL(@VENDOR_LOT,@LOT_NUMBER), @QTY_IN, @DATE_IN, @STAGE_NAME, @STATUS, CURRENT_TIMESTAMP,1, @LOT_CATEGORY, 0, @DATE_CODE, @PO_NUMBER )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_NAME, PROCESS_TYPE, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
				VALUES ( @PART_NUMBER, @LOT_NUMBER, @FAB_LOT_NO, @vendor_name, @PS_TYPE,'New Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
		
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_QTY_BALANCE, IL_DATE_IN, EXPECTED_DATE_OUT, TIMESTAMP, IL_Lead_Count, IL_Package_Type, IL_Test_Program) VALUES (@LOT_ID, @QTY_IN, @QTY_IN, @DATE_IN, @EXPECTED_DATE_OUT, CURRENT_TIMESTAMP, @Lead_Count, @Package_Type, @Test_Program )		
				
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @STATUS, QTY_IN = @QTY_IN, STAGE_NAME = @STAGE_NAME, LOT_CATEGORY = @LOT_CATEGORY, Lot_Drop_Out = 0, FAB_LOT_NUMBER = @FAB_LOT_NO, LOT_DATE_CODE = @DATE_CODE, PO_NUMBER = @PO_NUMBER, VENDOR_LOT_NUMBER = ISNULL(@VENDOR_LOT,@LOT_NUMBER) WHERE LOT_ID = @LOT_ID

		UPDATE INVENTORY_LOT SET EXPECTED_DATE_OUT = @EXPECTED_DATE_OUT, IL_QTY_IN = @QTY_IN, IL_QTY_BALANCE = @QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP, IL_Lead_Count = @Lead_Count, IL_Package_Type = @Package_Type, IL_Test_Program = @Test_Program WHERE LOT_ID = @LOT_ID
	END

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NO, @QTY_IN, @DATE_IN
	
 END